"""
Case Type   : openGauss 支持忽略PLSQL间的依赖进行创建
Case Name   : 包/存储过程的类型使用db.schema.tb%RowType，部分测试点合理报错
Create At   : 2023.11.15
Owner       : zhanghuan96
Description :
    1. 使用gs_guc reload方式设置参数为plpgsql_dependency
    2. 创建数据库和schema
    3. 创建表
    4. 创建包,包体中定义类型的成员类型使用db.schema.tb%RowType
    5. 查询gs_dependencies_obj系统表和gs_dependencies系统表
    6. 创建包，包体中定义类型的成员类型使用db1.schema.tb%RowType
    7. 创建存储过程，参数类型使用db.schema.tb%RowType成功
    8. 创建存储过程，参数类型使用db1.schema.tb%RowType
    9. 清理环境
Expect      :
    1. 成功
    2. 成功
    3. 成功
    4. 成功
    5. 成功
    6. 报错，不支持跨库使用
    7. 成功
    8. 报错，不支持跨库使用
    9. 成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class PlpgsqlDependency(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.common = Common()
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.db_name = "db_guc0049"
        self.schema_name = "schema_guc0049"
        self.tb_name = "tb_guc0049"
        self.pkg_name = "pkg_guc0049"
        self.default_value = self.common.show_param('behavior_compat_options')

    def test_guc(self):
        text = '--step1:使用gs_guc reload方式设置参数为plpgsql_dependency;' \
               'expect:成功--'
        self.log.info(text)
        mod_msg = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            "behavior_compat_options "
                                            "='plpgsql_dependency'")
        self.log.info(mod_msg)
        self.assertTrue(mod_msg)
        sql_cmd = "show behavior_compat_options;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual('plpgsql_dependency',
                         sql_res.splitlines()[-2].strip(),
                         '执行失败:' + text)

        text = '--step2:创建数据库和schema;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''drop database if exists {self.db_name};
        create database {self.db_name};
        drop database if exists {self.db_name}_01;
        create database {self.db_name}_01;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual(2, sql_res.count('CREATE DATABASE'), '执行失败:' + text)

        text = '--step3:创建表;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''drop schema if exists {self.schema_name} cascade;
        create schema {self.schema_name};
        set current_schema = {self.schema_name};
        drop table if exists {self.tb_name};\
        create table {self.tb_name} (sno int, \
        name varchar, sex varchar,cno int);
        insert into {self.tb_name} values(1,'zhang','M',1);
        insert into {self.tb_name} values(1,'zhang','M',1);
        insert into {self.tb_name} values(2,'wangwe','M',2);
        insert into {self.tb_name} values(3,'liu','F',3);
        '''
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('CREATE SCHEMA', sql_res, '查询失败')
        self.assertIn('SET', sql_res, '查询失败')
        self.assertIn('CREATE TABLE', sql_res, '查询失败')
        self.assertEqual(4, sql_res.count('INSERT 0 1'), '执行失败:' + text)

        text = '--step4:创建包,包体中定义类型的成员类型使用db.schema.tb%RowType;' \
               'expect:成功--'
        self.log.info(text)
        sql_cmd = f'''set current_schema = {self.schema_name};
        create or replace package {self.pkg_name}
        is
            type r1 is record(a int, \
            c {self.db_name}.{self.schema_name}.{self.tb_name}%RowType);
            procedure proc_0049_01();
        end {self.pkg_name};
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('CREATE PACKAGE', sql_res, '查询失败')

        text = '--step5:查询系统表;expect:成功--'
        self.log.info(text)
        sql_cmd = f"set current_schema = {self.schema_name};" \
                  f"select * from gs_dependencies_obj where " \
                  f"schemaname='{self.schema_name}' order by schemaname, " \
                  f"packagename, name, type;" \
                  f"select schemaname, packagename, objectname, refobjpos " \
                  f"from gs_dependencies where " \
                  f"schemaname='{self.schema_name}' order by schemaname, " \
                  f"packagename, objectname, refobjpos;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('schema_guc0049 | null        |    3 | tb_guc0049',
                      sql_res, '查询失败')
        self.assertIn('schema_guc0049 | pkg_guc0049 | r1         |         1',
                      sql_res, '查询失败')

        text = '--step6:创建包，包体中定义类型的成员类型使用db1.schema.tb%RowType;' \
               'expect:报错，不支持跨库使用--'
        self.log.info(text)
        sql_cmd = f'''set current_schema = {self.schema_name};
        create or replace package {self.pkg_name}_01
        is
            type r2 is record(a int, \
            c {self.db_name}_01.{self.schema_name}.{self.tb_name}%RowType);
            procedure proc_0049_02();
        end {self.pkg_name}_01;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('ERROR:  cross-database references are not implemented',
                      sql_res, '查询失败')

        text = '--step7:创建存储过程，参数类型使用db.schema.tb%RowType;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''set current_schema = {self.schema_name};
        create or replace procedure proc_0049_03(v_1 \
        {self.db_name}.{self.schema_name}.{self.tb_name}%RowType)
        as
        begin
            NULL;
        end;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('CREATE PROCEDURE', sql_res, '查询失败')

        text = '--step8:创建存储过程，参数类型使用db.schema.tb%RowType;' \
               'expect:报错，不支持跨库使用--'
        self.log.info(text)
        sql_cmd = f'''set current_schema = {self.schema_name};
        create or replace procedure proc_0049_04(v_1  \
        {self.db_name}_01.{self.schema_name}.{self.tb_name}%RowType)
        as
        begin
            NULL;
        end;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('ERROR:  cross-database references are not implemented',
                      sql_res, '查询失败')

    def tearDown(self):
        text = '----step9:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"drop schema if exists {self.schema_name} cascade;" \
                  f"reset current_schema;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        mod_msg = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f"behavior_compat_options "
                                            f"='{self.default_value}'")
        self.log.info(mod_msg)
        self.assertTrue(mod_msg)
        self.assertEqual(2, sql_res.count('DROP DATABASE'),  '删除失败')
        self.assertIn('DROP SCHEMA', sql_res, '删除失败')
        self.assertIn('RESET', sql_res, '删除失败')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
